﻿using FundHelper.Model;
using FundHelper.Utility;
using OfficeOpenXml;
using System.Data;
using System.Diagnostics;
using System.Numerics;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;


namespace FundHelper
{
    internal partial class ExploerData
    {
        public static DataTable ReadExcel(string filePath)
        {
            // 检查文件是否存在
            if (!File.Exists(filePath))
            {
                throw new FileNotFoundException($"{filePath}:Excel文件不存在");
            }
            DataTable dataTable = new DataTable();
            // 创建一个FileInfo对象
            FileInfo fileInfo = new FileInfo(filePath);
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // 如果你打算在非商业环境中使用EPPlus

            // 使用EPPlus加载Excel包
            using (ExcelPackage package = new ExcelPackage(fileInfo))
            {
                // 获取Excel工作表
                ExcelWorksheet worksheet = package.Workbook.Worksheets[0]; // 获取第一个工作表

                // 读取列标题
                int rowCount = worksheet.Dimension.Rows;
                int colCount = worksheet.Dimension.Columns;

                for (int col = 1; col <= colCount; col++)
                {
                    dataTable.Columns.Add(worksheet.Cells[1, col].Text);// 假设第一行是标题行
                }
                // 从第二行开始读取数据行
                for (int row = 2; row <= rowCount; row++)
                {
                    DataRow dataRow = dataTable.NewRow();
                    for (int col = 1; col <= colCount; col++)
                    {
                        // 这里假设所有单元格都是字符串类型，你可以根据需要添加类型转换逻辑
                        dataRow[dataTable.Columns[col - 1].ColumnName] = worksheet.Cells[row, col].Text;
                    }
                    dataTable.Rows.Add(dataRow);
                }
                return dataTable;
            }
        }

        public static List<T> ReadExcel<T>(string filePath, Dictionary<string, string> columnMappings) where T : new()
        {

                var excelData = ReadExcel(filePath);
                var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
                Dictionary<string, string> keyValuePairs = new Dictionary<string, string>();
                List<T> list = new List<T>();
                foreach (DataColumn col in excelData.Columns)
                {
                    if (columnMappings.TryGetValue(col.ColumnName, out string? propertyName))
                    {
                        keyValuePairs.Add(propertyName, col.ColumnName);
                    }
                }
                for (int i = 0; i < excelData.Rows.Count; i++)
                {

                    if (string.IsNullOrEmpty(excelData.Rows[i][0].ToString()))
                        break;
                    T obj = new T();
                    foreach (var propertyName in keyValuePairs.Keys)
                    {
                        var colName = keyValuePairs[propertyName];
                        
                        if (properties.FirstOrDefault(p=>p.Name==propertyName) is PropertyInfo property)
                        {
                            object value = excelData.Rows[i][colName];
                            // 这里需要处理类型转换，因为Excel中的所有值都是字符串类型，而类的属性可能是其他类型
                            // 这里为了简化，只处理字符串和整数类型，你可以根据需要扩展
                            if (property.PropertyType == typeof(string))
                            {
                                property.SetValue(obj, value?.ToString());
                            }
                            else if (property.PropertyType == typeof(int) && int.TryParse(value?.ToString(), out int intValue))
                            {
                                property.SetValue(obj, intValue);
                            }
                            else if (property.PropertyType == typeof(double) && double.TryParse(value?.ToString(), out double doubleValue))
                            {
                                property.SetValue(obj, doubleValue);

                            }
                        }
                    }
                    list.Add(obj);
                }
                return list;
            }
        public static IEnumerable<FundHistory> Generagte(string filePath) 
        {
            List<FundHisBase> list = new List<FundHisBase>();

            var columnMappings = new Dictionary<string, string>
            {
                { "证券名称", "id" },
                { "交易时间", "date" },
                { "收盘价", "dwjz" },
                { "最低价", "low" },
                { "最高价", "high" },
                {"涨跌幅%","zzl" }
            };

            List<FundHisBase> dataList = ReadExcel<FundHisBase>(filePath, columnMappings);
            var fundHistories = (from d in dataList select (FundHistory)d ).ToArray();
            return fundHistories;
        }
        public static IEnumerable<FundHistory> Generagte(params string[] filePaths) 
        {
            List<FundHisBase> list = new List<FundHisBase>();
            List<FundHistory> listHistories = new List<FundHistory>();
            var columnMappings = new Dictionary<string, string>
            {
                { "证券名称", "id" },
                { "交易时间", "date" },
                { "收盘价", "dwjz" },
                { "最低价", "low" },
                { "最高价", "high" },
                {"涨跌幅%","zzl" }
            };
            foreach (var filePath in filePaths)
            {
                List<FundHisBase> dataList = ReadExcel<FundHisBase>(filePath, columnMappings);
                var fundHistories = (from d in dataList select (FundHistory)d).ToList();
                listHistories.AddRange(fundHistories);
            }
            return listHistories.ToArray();
        }
        public static void GetTest2(IEnumerable<ICanToTuple> his, int testNum, bool addHead = true, string savePath = "我的验证.txt", int removeEnd = 1, bool normalization = false)
        {
            // 克隆并排序历史数据
            var quotes = his.ToQuote();
            quotes = quotes.OrderByDescending(a => a.Date);

            int[] periods = [2, 3, 5, 7, 8, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47, 53, 55, 59, 61, 67, 71, 73, 79, 83, 89 ];
            int[] periodsMacd = [1,..periods];
            int[] statisticPeriod = [5, 13, 21,55];
            Dictionary<IndicatorEnum, ExtractionSetting> extracSettings = new Dictionary<IndicatorEnum, ExtractionSetting>();
            extracSettings.Add(IndicatorEnum.ZZL, new ExtractionSetting([1, 5, 13, 21]));
            extracSettings.Add(IndicatorEnum.RANK,new ExtractionSetting(periods));
            extracSettings.Add(IndicatorEnum.MACD, new ExtractionSetting([1]) { LagDay = [1, 5, 13, 21] });
            

            extracSettings.Add(IndicatorEnum.MACDSta,new ExtractionSetting(statisticPeriod));
            extracSettings.Add(IndicatorEnum.MACDPLUSE,new ExtractionSetting([9]));
            extracSettings.Add(IndicatorEnum.KDJ, new ExtractionSetting([1]) { LagDay = [0, 5, 13, 21] });
            extracSettings.Add(IndicatorEnum.BIAS, new ExtractionSetting([6, 12, 24]) { LagDay = [0, 5, 13, 21] });
            extracSettings.Add(IndicatorEnum.AF, new ExtractionSetting([5, 10]));


            var t1 = GetOutput(his, testNum, removeEnd, extracSettings, out (Statistic<double> mSt, Statistic<double> deaSt,Statistic<double> dif) macdStatic);
            extracSettings[IndicatorEnum.MACDPLUSE] = new ExtractionSetting([5]);
            var head = GeneragteHeader(extracSettings);
            var macdPs = t1.GetItemData(IndicatorEnum.MACDPLUSE);

            var macdPLines = Standardization(macdPs, 0);
            macdPLines = macdPLines.Select(x => x.ToList().GetRange(0, 5).ToArray()).ToList();
            t1.Replace(macdPLines, IndicatorEnum.MACDPLUSE);


            for (int i = 0; i < 4; i++)
            {
                var macdst = t1.GetItemData(IndicatorEnum.MACDSta);
                var macdstPLines = Standardization(macdst, i, macdStatic.mSt);
                t1.Replace(macdstPLines, IndicatorEnum.MACDSta);
            }
            for (int i = 0; i < 1; i++)
            {
                var macdst = t1.GetItemData(IndicatorEnum.MACD);
                var macdstPLines = Standardization(macdst, i, macdStatic.mSt);
                t1.Replace(macdstPLines, IndicatorEnum.MACD);
            }

            var lines = BuildOutputLines(t1);
            WriteToFile(lines, savePath, addHead, head);
        }

        private static string GeneragteHeader(Dictionary<IndicatorEnum, ExtractionSetting> extracSettings)
        {

            StringBuilder headBuilder = new StringBuilder();
            headBuilder.Append("date,id");
            foreach (var item in extracSettings)
            {
                int[] period = item.Value.period;
                int[] lagDays = item.Value.LagDay;

                switch (item.Key)
                {
                    case IndicatorEnum.ZZL:
                        foreach (var value in period)
                            headBuilder.Append($",zzl{value}");
                        break;
                    case IndicatorEnum.BIAS:
                        foreach (var value in period)
                            foreach(var lag in lagDays)
                                headBuilder.Append($",Bias{value}_{lag}");
                        break;
                    case IndicatorEnum.MACD:
                        foreach (var lag in lagDays)
                            headBuilder.Append($",macd{lag},macdRatio{lag}");
                        break;
                    case IndicatorEnum.MACDSta:
                        foreach (var value in period)
                            headBuilder.Append($",macdMin{value},macdMid{value},macdMax{value},macdMean{value},macdVariance{value}");
                        break;
                    case IndicatorEnum.AF:
                        foreach (var value in period)
                            headBuilder.Append($",af{value},af{value}min,af{value}max");
                        break;
                    case IndicatorEnum.KDJ:
                        foreach (var value in lagDays)
                            headBuilder.Append($",K{value},D{value},J{value}");
                        break;
                    case IndicatorEnum.RANK:
                        foreach (var value in period)
                            headBuilder.Append($",rank{value}");
                        break;
                    case IndicatorEnum.MACDPLUSE:
                        for(int value=0;value< period.First(); value++)
                            headBuilder.Append($",MArea_{value},MAreDay_{value}");
                        break;
                    case IndicatorEnum.MACDCBIAS:
                    default:
                        break;
                }
            }
            Regex regex = new Regex(@"([a-zA-Z])_{0,1}0");  // 匹配字母后跟着 "0"
            string str = headBuilder.ToString();
            str=regex.Replace(str, new MatchEvaluator(a => a.Groups[1].Value))
                .Replace("_0","");


            return str;
        }

        private static List<string[]> Standardization(IEnumerable<string[]> sourceData, int dataIdx,char delimiter=',')
        {
            List<string[]> lines = new List<string[]>();
            foreach (string[] data in sourceData)
            {
                string[] back = new string[data.Length];
                data.CopyTo(back,0);
                //先获取有数据行。首先必须逗号数量大于dataIdx
                var numString = data.Where(x => x.Split(delimiter).Length > dataIdx&& x.Split(delimiter)[dataIdx].Length>1);
                //先获取这行所有的数
                var samples = numString.Select(x => double.Parse(x.Split(delimiter)[dataIdx]));
                var standardizedValues =samples.Select(x => PrepareData.Standardization(samples,x).ToString());
                var kp = numString.Where(x => x.Length > 1).Zip(standardizedValues);
                foreach (var item in kp)
                {
                    var parts=item.First.Split(delimiter);
                    parts[dataIdx] = item.Second.ToString();
                    
                    string info= string.Join(delimiter, parts);
                    for (int i = 0; i < data.Length; i++) {
                        if (back[i] == item.First)
                            back[i] = info;
                    }
                }
                lines.Add(back);
            }
            return lines;
        }

        private static List<string[]> Standardization<T>(IEnumerable<string[]> sourceData, int dataIdx, Statistic<T> statistic, char delimiter = ',') where T : INumber<T>
        {
            List<string[]> lines = new List<string[]>();
            foreach (string[] data in sourceData)
            {
                string[] back = new string[data.Length];
                data.CopyTo(back, 0);
                //先获取有数据行。首先必须逗号数量大于dataIdx
                if (Enumerable.Range(0, data.Length)
                    .Select(i => (OIdx: i, Str: data[i].Split(delimiter)))
                    .Where(x => x.Str.Length > dataIdx && x.Str[dataIdx].Length > 1)
                    is IEnumerable<(int OIdx, string[] Str)> numString
                    )
                {
                    //先获取这行所有的数
                    var samples = numString
                        .Select(x => (OId: x.OIdx, Num: T.Parse(x.Str[dataIdx], System.Globalization.NumberStyles.Number, null)))
                        .Where(x => x.Num != null);

                    var standardizedValues = samples.Select(x => PrepareData.Standardization<T>(statistic, x.Num).ToString());
                    var kp = numString.Zip(standardizedValues, (a, b) => (a.OIdx,a.Str, NewStr: b));
                    foreach (var item in kp)
                    {
                        if (item.NewStr == null)
                            continue;
                        item.Str[dataIdx] = item.NewStr;
                        string info = string.Join(delimiter, item.Str);
                        back[item.OIdx] = info;
                    }
                    lines.Add(back);
                }
            }
            return lines;
        }


        public static void CalMark2(IEnumerable<ICanToTuple> his, bool addHead = true, string savePath = "我的计算.csv", int removeEnd = 10, int printNum = 10, bool normalization = true)
        {
            // 克隆并排序历史数据
            var quotes = his.ToQuote();
            quotes = quotes.OrderByDescending(a => a.Date);

            int[] periods = { 2, 3, 5, 7, 8, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47, 53, 55, 59, 61, 67, 71, 73, 79, 83, 89 };
            int[] periodsMacd = { 1, 2, 3, 5, 7, 8, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47, 53, 55, 59, 61, 67, 71, 73, 79, 83, 89 };
            int[] statisticPeriod = [5, 13, 21, 55];
            Dictionary<IndicatorEnum, ExtractionSetting> extracSettings = new Dictionary<IndicatorEnum, ExtractionSetting>();
            extracSettings.Add(IndicatorEnum.ZZL, new ExtractionSetting([1, 5, 13, 21]));
            //extracSettings.Add(IndicatorEnum.RANK,new ExtractionSetting(periods));
            extracSettings.Add(IndicatorEnum.MACD, new ExtractionSetting([1]){ LagDay = [0, 5, 13, 21] });
            extracSettings.Add(IndicatorEnum.MACDSta, new ExtractionSetting(statisticPeriod));
            extracSettings.Add(IndicatorEnum.MACDPLUSE, new ExtractionSetting([9]));
            extracSettings.Add(IndicatorEnum.KDJ, new ExtractionSetting([1]) { LagDay = [0, 5, 13, 21] });
            extracSettings.Add(IndicatorEnum.BIAS, new ExtractionSetting([6, 12, 24]) { LagDay = [0, 5, 13, 21] });
            extracSettings.Add(IndicatorEnum.AF, new ExtractionSetting([5, 10]));
            Stopwatch stopwatch = Stopwatch.StartNew();
            var t1 = GetOutput(his, his.ToList().Count() - 90 - removeEnd, removeEnd, extracSettings, out (Statistic<double> mSt, Statistic<double> deaSt, Statistic<double> dif) macdStatic);
            Debug.WriteLine(stopwatch.ElapsedTicks);
            extracSettings[IndicatorEnum.MACDPLUSE] = new ExtractionSetting([5]);
            var head = GeneragteHeader(extracSettings);

            var macdPs = t1.GetItemData(IndicatorEnum.MACDPLUSE);

            var macdPLines = Standardization(macdPs, 0);
            macdPLines = macdPLines.Select(x => x.ToList().GetRange(0, 5).ToArray()).ToList();
            t1.Replace(macdPLines, IndicatorEnum.MACDPLUSE);


            for (int i = 0; i < 4; i++)
            {
                var macdst = t1.GetItemData(IndicatorEnum.MACDSta);
                var macdstPLines = Standardization(macdst, i, macdStatic.mSt);
                t1.Replace(macdstPLines, IndicatorEnum.MACDSta);
            }
            for (int i = 0; i < 1; i++)
            {
                var macdst = t1.GetItemData(IndicatorEnum.MACD);
                var macdstPLines = Standardization(macdst, i, macdStatic.mSt);
                t1.Replace(macdstPLines, IndicatorEnum.MACD);
            }
            var lines = BuildOutputLines(t1);
            WriteToFile(lines, savePath, addHead, head);
            lines.Take(printNum).ToList().ForEach(line => Console.WriteLine(line));
        
            
        }
    }
}
